﻿using Dapper;
using Domain.Entity;
using Domain.Entity.ord;
using Domain.Entity.Out;
using Domain.Entity.search;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Sale_hRepository : ISale_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加销售单
        public async Task<int> AddSale_h(Sale_h sale_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ord_sale_h](sale_code, sale_date, cust_id, cust_name, cust_linkman, cust_linkphone, sale_depId, sale_depName, sale_userId, sale_userName, sale_categories, sale_num, sale_total, sale_status, create_name, create_time, remark)
                                values('{sale_H.sale_code}','{sale_H.sale_date}',{sale_H.cust_id},'{sale_H.cust_name}','{sale_H.cust_linkman}','{sale_H.cust_linkphone}',{sale_H.sale_depId},'{sale_H.sale_depName}',{sale_H.sale_userId},'{sale_H.sale_userName}',{sale_H.sale_categories},{sale_H.sale_num},'{sale_H.sale_total}',{sale_H.sale_status},'{sale_H.create_name}','{sale_H.create_time}','{sale_H.remark}');
                                 SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql); //返回当前数据的主键";
            }
        }


        //审核销售单
        public async Task<int> ApproverSale_h(int saleid, int status, string approvername, string approvertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_sale_h] set sale_status={status},approver_name='{approvername}',approver_time='{approvertime}' where sale_id={saleid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //删除销售单
        public async Task<int> DeleteSale_h(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_sale_h] set is_delete=1 where sale_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //高级查询
        public async Task<FenYe<Sale_h>> GetAdvancedSale_h(FenYe<Sale_h> fenYe, string? code, string? starttotal, string? endtotal, string? startdate, string? enddate, string? startnum, string? endnum, int? status, int? cust, string? custlink, int? dep, int? user, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_sale");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and sale_code like '%{code}%'";
                if (!string.IsNullOrEmpty(starttotal) || !string.IsNullOrEmpty(endtotal))
                    sqlparam += $" and  sale_total between '{starttotal}' and '{endtotal}'";
                if (!string.IsNullOrEmpty(startdate) || !string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  sale_date between '{startdate}' and '{enddate}'";
                if (!string.IsNullOrEmpty(startnum) || !string.IsNullOrEmpty(endnum))
                    sqlparam += $" and  sale_num between '{startnum}' and '{endnum}'";
                if (status != null && status != 0)
                    sqlparam += $" and  sale_status={status}";
                if (cust != null && cust != 0)
                    sqlparam += $" and  cust_id={cust}";
                if (!string.IsNullOrEmpty(custlink))
                    sqlparam += $" and cust_linkman like '%{custlink}%'";
                if (dep != null && dep != 0)
                    sqlparam += $" and  sale_depId={dep}";
                if (user != null && user != 0)
                    sqlparam += $" and  sale_userId={user}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Sale_h> list = sqlConnection.Query<Sale_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据货品id查询销售记录
        public async Task<List<Sale_h>> GetAllSale_hBypid(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select h.sale_id,sale_code,sale_date,cust_name,cust_linkman,cust_linkphone,sale_depName,sale_userName,sale_num,sale_total,create_name,create_time,approver_name,approver_time from [dbo].[ord_sale_d] d
                                inner join [dbo].[ord_sale_h] h on h.sale_id=d.sale_id
                                where proc_id={procid}";
                return sqlConnection.Query<Sale_h>(sql).ToList();
            }
        }



        //根据销售编号查询主键id和销售总数
        public async Task<Sale_h> Getid(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select sale_id,sale_num,sale_outstockOK_num,sale_outstockNO_num from [dbo].[ord_sale_h] where sale_code='{code}'";
                return await sqlConnection.QueryFirstOrDefaultAsync<Sale_h>(sql);
            }
        }


        //分页查询销售单
        public async Task<FenYe<Sale_h>> GetSale_h(FenYe<Sale_h> fenYe, string? code, int? status, int? cust, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_sale");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and sale_code like '%{code}%'";
                if (cust != 0 && cust != null)
                    sqlparam += $" and cust_id ={cust}";
                if (status != null && status != 0)
                    sqlparam += $" and  sale_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  sale_date between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Sale_h> list = sqlConnection.Query<Sale_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据id查询销售单
        public async Task<Sale_h> GetSale_HById(int id)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[ord_sale_h] where sale_id={id}";
                return await sqlConnection.QueryFirstAsync<Sale_h>(sql);
            }
        }


        //查询所有销售单
        public async Task<List<Sale_h>> GetSale_Hs()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select * from [dbo].[ord_sale_h] where is_delete=0 and (sale_status=7 or sale_status=8 or sale_status=6 or sale_status=10)";
                return sqlConnection.Query<Sale_h>(sql).ToList();
            }
        }


        //销售统计
        public async Task<SaleTJResult> Sale_hTJ(string? timeType, string? code, int? cust, int? user, int? dep, string? starttime, string? endtime, int? page, int? pagesize)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var where = " h.is_delete = 0  and  sale_status=6";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
                {
                    where += " AND h.sale_date BETWEEN @starttime AND @endtime";
                    parameters.Add("@starttime", starttime);
                    parameters.Add("@endtime", endtime);
                }
                if (!string.IsNullOrEmpty(code))
                {
                    where += " AND h.sale_code LIKE @code";
                    parameters.Add("@code", $"%{code}%");
                }
                if (cust != null && cust != 0)
                {
                    where += " AND h.cust_id = @cust";
                    parameters.Add("@cust", cust);
                }
                if (user != null && user != 0)
                {
                    where += " AND h.sale_userId = @user";
                    parameters.Add("@user", user);
                }
                if (dep != null && dep != 0)
                {
                    where += " AND h.sale_depId = @dep";
                    parameters.Add("@dep", dep);
                }
                if (!string.IsNullOrEmpty(timeType))
                {
                    switch (timeType)
                    {
                        case "today":
                            where += " AND CONVERT(date, h.sale_date) = CONVERT(date, GETDATE())";
                            break;
                        case "yesterday":
                            where += " AND CONVERT(date, h.sale_date) = CONVERT(date, DATEADD(day, -1, GETDATE()))";
                            break;
                        case "week":
                            where += " AND DATEPART(week, h.sale_date) = DATEPART(week, GETDATE()) AND YEAR(h.sale_date) = YEAR(GETDATE())";
                            break;
                        case "lastWeek":
                            where += " AND DATEPART(week, h.sale_date) = DATEPART(week, DATEADD(week, -1, GETDATE()))"
                                + " AND YEAR(h.sale_date) = YEAR(DATEADD(week, -1, GETDATE()))";
                            break;
                        case "month":
                            where += " AND MONTH(h.sale_date) = MONTH(GETDATE()) AND YEAR(h.sale_date) = YEAR(GETDATE())";
                            break;
                        case "lastMonth":
                            where += " AND MONTH(h.sale_date) = MONTH(DATEADD(month, -1, GETDATE()))"
                                + " AND YEAR(h.sale_date) = YEAR(DATEADD(month, -1, GETDATE()))";
                            break;
                    }
                }

                int skip = ((page ?? 1) - 1) * (pagesize ?? 10);

                // 1. 查询总数（只查主单表）
                string countSql = $@"
                    SELECT COUNT(1)
                    FROM [dbo].[ord_sale_h] h
                    WHERE {where}
                ";
                int total = await sqlConnection.ExecuteScalarAsync<int>(countSql, parameters);

                // 2. 查询统计（全量，不分页）
                string statSql = $@"
                    SELECT 
                        ISNULL(SUM(d.proc_saled_total), 0) AS totalAmount,
                        COUNT(DISTINCT h.cust_id) AS totalCustomer,
                        COUNT(DISTINCT h.sale_id) AS totalOrder
                    FROM [dbo].[ord_sale_h] h
                    LEFT JOIN [dbo].[ord_sale_d] d ON h.sale_id = d.sale_id
                    WHERE {where}
                ";
                var stat = await sqlConnection.QueryFirstOrDefaultAsync(statSql, parameters);

                // 3. 查询当前页数据
                string sql = $@"
                    SELECT h.sale_id, h.sale_code, h.sale_date, h.cust_name, h.sale_depName, h.sale_userName, 
                           d.saled_id, d.proc_name, p.proc_code, d.proc_saled_num, d.proc_saled_total
                    FROM [dbo].[ord_sale_h] h
                    LEFT JOIN [dbo].[ord_sale_d] d ON h.sale_id = d.sale_id
                    LEFT JOIN [dbo].[bs_product] p ON p.proc_id = d.proc_id
                    WHERE {where}
                    ORDER BY h.sale_id
                    OFFSET @skip ROWS FETCH NEXT @pagesize ROWS ONLY;
                ";

                parameters.Add("@skip", skip);
                parameters.Add("@pagesize", pagesize ?? 10);

                var list = (await sqlConnection.QueryAsync<SaleDto>(sql, parameters)).ToList();

                return new SaleTJResult
                {
                    list = list,
                    total = total,
                    totalAmount = stat?.totalAmount ?? 0,
                    totalCustomer = stat?.totalCustomer ?? 0,
                    totalOrder = stat?.totalOrder ?? 0
                };
            }
        }


        //更新出库数量及状态
        public async Task<int> UpdateNum(int saleid, int oknum, int nonum, int statu)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_h] set sale_outstockOK_num={oknum},sale_outstockNo_num={nonum},sale_status={statu} where sale_id={saleid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //更新退货数量及状态
        public async Task<int> UpdateRNum(int saleid, int returnnum, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_h] set sale_returned_num={returnnum},sale_status={status} where sale_id={saleid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        
        public async Task<int> UpdateRNum(int saleid, int returnnum, int status, int newnum)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_h] set sale_returned_num={returnnum},sale_status={status},sale_num={newnum} where sale_id={saleid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改销售单
        public async Task<int> UpdateSale_h(Sale_h sale_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_h] set sale_date='{sale_H.sale_date}',cust_id={sale_H.cust_id},cust_name='{sale_H.cust_name}',cust_linkman='{sale_H.cust_linkman}',cust_linkphone='{sale_H.cust_linkphone}',sale_depId={sale_H.sale_depId},sale_depName='{sale_H.sale_depName}',sale_userId={sale_H.sale_userId},sale_userName='{sale_H.sale_userName}',sale_categories={sale_H.sale_categories},sale_num={sale_H.sale_num},sale_total='{sale_H.sale_total}',sale_status={sale_H.sale_status},update_name='{sale_H.update_name}',update_time='{sale_H.update_time}',remark='{sale_H.remark}' where sale_id={sale_H.sale_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改单据状态
        public async Task<int> UpdateStatus(int saleid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_h] set sale_status={status} where sale_id={saleid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
